﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class frm_Add_PODetails : Form
    {
        public frm_Add_PODetails()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            getPOIDList();
            this.poID.Properties.AutoComplete = true;
            getPartNoList();
            this.partNo.Properties.AutoComplete = true;
            getTrasportIDList();
            this.transportationID.Properties.AutoComplete = true;
            getDeliveryList();
            this.deliveryID.Properties.AutoComplete = true;
        }

        ////////////////////////////////////////////////
        /// Get POID List to form
        //////////////////////////////////////////////// 
        private void getPOIDList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT ID FROM [241_PO] WHERE Finished=0 ORDER BY ID";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["ID"].ToString();
                    item.Value = dataReader["ID"].ToString();
                    this.poID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get POID list");
            }
        }        

        ////////////////////////////////////////////////
        /// Get PartNo List to form
        //////////////////////////////////////////////// 
        private void getPartNoList()
        {
            ComboboxItem poid = (ComboboxItem)poID.SelectedItem;
            string supplierid = null;
            //////////////////////////////////////
            /// Get SupplierID for selected POID
            /////////////////////////////////////
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT SupplierID FROM [241_PO] WHERE ID='" + poid.Value.ToString() + "'";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    supplierid = dataReader["SupplierID"].ToString();                    
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                supplierid = null;
            }
            
            /////////////////////////////////////////////////////////////
            /// Get PartNo compactable with Supplier ID in Quotation
            ////////////////////////////////////////////////////////////
            if (!string.IsNullOrEmpty(supplierid))
            {
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT PartNo FROM [222_Part] WHERE PartNo IN"
                        + " (SELECT PartNo FROM [241_Quotation] WHERE [241_Quotation].SupplierID='" + supplierid + "')";
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    while (dataReader.Read())
                    {
                        ComboboxItem item = new ComboboxItem();
                        item.Text = dataReader["PartNo"].ToString();
                        item.Value = dataReader["PartNo"].ToString();
                        this.partNo.Properties.Items.Add(item);
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();
                    myConnection.Close();
                }
                catch
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show("Cannot get PartNo list");
                }
            }
        }

        ////////////////////////////////////////////////
        /// Get TransportID List to form
        //////////////////////////////////////////////// 
        private void getTrasportIDList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT TransportID, TransportName FROM [222_Transport] ORDER BY TransportName";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["TransportName"].ToString();
                    item.Value = dataReader["TransportID"].ToString();
                    this.transportationID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Trasportation list");
            }
        }

        ////////////////////////////////////////////////
        /// Load Delivery list to form
        ////////////////////////////////////////////////
        private void getDeliveryList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT DeliveryTermID, DeliveryTermName FROM [222_DeliveryTerm] ORDER BY DeliveryTermName";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["DeliveryTermName"].ToString();
                    item.Value = dataReader["DeliveryTermID"].ToString();
                    this.deliveryID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Delivery list");
            }
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            ComboboxItem poid = (ComboboxItem)poID.SelectedItem;
            ComboboxItem partno = (ComboboxItem)partNo.SelectedItem;
            ComboboxItem transport = (ComboboxItem)transportationID.SelectedItem;
            ComboboxItem delivery = (ComboboxItem)deliveryID.SelectedItem;
            DateTime deliverydate;
            DateTime.TryParse(deliveryDate.DateTime.Date.ToString(), out deliverydate);
            int quantity = Int32.Parse(quantityValue.Text);
            int currencyID = -1;
            float unitPrice = 0;
            string supplierID = null;
            string paymentID = null; 
            try
            {
                myConnection.Open();
                
                ////////////////////////////////////////
                /// Get SupplierID and PaymentID
                ////////////////////////////////////////                
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT SupplierID, PaymentID FROM [241_PO] WHERE ID='" + poid.Value.ToString() + "'";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    supplierID = dataReader["SupplierID"].ToString();
                    paymentID = dataReader["PaymentID"].ToString();                    
                }
                dataReader.Close();
                ///////////////////////////////////////////////////
                /// Get CurrencyID and UnitPrice
                //////////////////////////////////////////////////                
                if (paymentID == "NoComercia")
                {
                    cmd_sql.CommandText = "SELECT CurrencyID FROM [222_Currency] WHERE CurrencySymbol='USD'";
                    dataReader = cmd_sql.ExecuteReader();
                    if (dataReader.Read())
                    {                        
                        currencyID = Int32.Parse(dataReader["CurrencyID"].ToString());
                    }
                    dataReader.Close();
                    unitPrice = 0;
                }
                else
                {
                    cmd_sql.CommandText = "SELECT CASE WHEN " + quantity + " >= Qty10 THEN Price10 "
                             + "WHEN " + quantity + " >= Qty9 THEN Price9 "
                             + "WHEN " + quantity + " >= Qty8 THEN Price8 "
                             + "WHEN " + quantity + " >= Qty7 THEN Price7 "
                             + "WHEN " + quantity + " >= Qty6 THEN Price6 "
                             + "WHEN " + quantity + " >= Qty5 THEN Price5 "
                             + "WHEN " + quantity + " >= Qty4 THEN Price4 "
                             + "WHEN " + quantity + " >= Qty3 THEN Price3 "
                             + "WHEN " + quantity + " >= Qty2 THEN Price2 "
                             + "ELSE Price1 END AS UnitPrice, CurrencyID "
                             + "FROM [241_Quotation] WHERE SupplierID='" + supplierID + "' AND PartNo='" + partno.Value.ToString() + "'";
                    dataReader = cmd_sql.ExecuteReader();                    
                    if (dataReader.Read())
                    {                        
                        unitPrice = float.Parse(dataReader["UnitPrice"].ToString());                        
                        currencyID = Int32.Parse(dataReader["CurrencyID"].ToString());                        
                    }
                    dataReader.Close();
                }
                cmd_sql.Dispose();                
                ////////////////////////////////////////////////////////////
                /// Insert to PODetails
                ////////////////////////////////////////////////////////////               
                string sql = "INSERT INTO [241_PODetails]"
                    + " (POID, OrderNo, PartNo, Quantity, UnitPrice, CurrencyID, DeliveryDate, TransportID, DeliveryTermID, Note, Purpose)"
                    + " VALUES ('" + poid.Value.ToString() + "', '" + orderNo.Text + "', @partno,"
                    + " " + quantity + ", " + unitPrice + ", " + currencyID + ", @deliverydate,"
                    + " '" + transport.Value.ToString() + "', '" + delivery.Value.ToString() + "',"
                    + " '" + noteValue.Text + "', '" + purposeValue.Text + "')";                
                cmd_sql = new SqlCommand(sql, myConnection);
                cmd_sql.Parameters.Add("@partno", SqlDbType.NVarChar).Value = partno.Value.ToString();
                cmd_sql.Parameters.Add("@deliverydate", SqlDbType.DateTime).Value = deliverydate.Date;
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                
                /////////////////////////////////////////////
                /// Set POID to not Finished
                /////////////////////////////////////////////
                sql = "UPDATE [241_PO] SET Finished=0, Cancelled=0 WHERE ID='" + poid.Value.ToString() + "' AND Finished=1";
                cmd_sql = new SqlCommand(sql, myConnection);
                cmd_sql.ExecuteNonQuery();
                
                cmd_sql.Dispose();
                myConnection.Close();
                MessageBox.Show("PODetails added success.");
                clearForm();
            }
            catch //(Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                //MessageBox.Show(ex.Message);
                MessageBox.Show("Cannot add PODetails. Please try again.");
            }            
        }

        private void poID_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.partNo.Properties.Items.Clear();
            getPartNoList();
        }

        private void clearForm()
        {
            foreach (Control c in this.Controls)
            {
                if (c is DevExpress.XtraEditors.TextEdit)
                {
                    ((DevExpress.XtraEditors.TextEdit)c).Text = null;
                }

                if (c is DevExpress.XtraEditors.ComboBoxEdit)
                {
                    ((DevExpress.XtraEditors.ComboBoxEdit)c).SelectedIndex = -1;
                }

                if (c is System.Windows.Forms.MaskedTextBox)
                {
                    ((System.Windows.Forms.MaskedTextBox)c).Text = null;
                }
            }
        }
    }
}
